package com.xingqing.repository;

import com.xingqing.domain.entity.Emp;
import com.xingqing.domain.vo.EmpVO;
import com.xingqing.domain.vo.JobStatisticsVO;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface EmpMapper {
    
    @Select("SELECT e.*, d.name as dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id ORDER BY e.create_time DESC")
    @Results({
        @Result(column = "dept_name", property = "deptName")
    })
    List<EmpVO> selectAllWithDept();
    
    @Select("SELECT e.*, d.name as dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.id = #{id}")
    @Results({
        @Result(column = "dept_name", property = "deptName")
    })
    EmpVO selectByIdWithDept(Long id);
    
    @Select("SELECT e.*, d.name as dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.dept_id = #{deptId}")
    @Results({
        @Result(column = "dept_name", property = "deptName")
    })
    List<EmpVO> selectByDeptIdWithDept(Long deptId);
    
    @Select("SELECT * FROM emp ORDER BY create_time DESC")
    List<Emp> selectAll();
    
    @Select("SELECT * FROM emp WHERE id = #{id}")
    Emp selectById(Long id);
    
    @Select("SELECT * FROM emp WHERE dept_id = #{deptId}")
    List<Emp> selectByDeptId(Long deptId);
    
    @Insert("INSERT INTO emp (password, name, gender, phone, job, salary, image, entrydate, dept_id, create_time, update_time) " +
            "VALUES (#{password}, #{name}, #{gender}, #{phone}, #{job}, #{salary}, #{image}, #{entrydate}, #{deptId}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(Emp emp);
    
    @Update("UPDATE emp SET password = #{password}, name = #{name}, gender = #{gender}, phone = #{phone}, " +
            "job = #{job}, salary = #{salary}, image = #{image}, entrydate = #{entrydate}, " +
            "dept_id = #{deptId}, update_time = #{updateTime} WHERE id = #{id}")
    void updateById(Emp emp);
    
    @Delete("DELETE FROM emp WHERE id = #{id}")
    void deleteById(Long id);
    
    @Delete("DELETE FROM emp WHERE dept_id = #{deptId}")
    void deleteByDeptId(Long deptId);
    
    /**
     * 统计各个职位的员工数量
     */
    @Select("SELECT job, COUNT(*) as count FROM emp GROUP BY job ORDER BY job")
    @Results({
        @Result(column = "job", property = "job"),
        @Result(column = "count", property = "count")
    })
    List<JobStatisticsVO> countByJob();
    
    /**
     * 统计各个部门的员工数量
     */
    @Select("SELECT d.name as dept_name, COUNT(e.id) as count " +
            "FROM dept d LEFT JOIN emp e ON d.id = e.dept_id " +
            "GROUP BY d.id, d.name ORDER BY d.id")
    List<Map<String, Object>> countByDept();
    
    /**
     * 统计男女员工数量
     */
    @Select("SELECT " +
            "CASE WHEN gender = 1 THEN '男' WHEN gender = 0 THEN '女' ELSE '未知' END as gender_name, " +
            "COUNT(*) as count " +
            "FROM emp GROUP BY gender ORDER BY gender")
    List<Map<String, Object>> countByGender();
}